package com.cqgcxy.shopping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cqgcxy.shopping.common.Pager;
import com.cqgcxy.shopping.common.CommException;
import com.cqgcxy.shopping.common.JdbcUtils;
import com.cqgcxy.shopping.domain.Goods;


public class GoodDao {
	private Logger logger=LoggerFactory.getLogger(getClass());
	
	private Goods rowMap (ResultSet rs) throws SQLException{
		Goods good =new Goods();
		good.setId(rs.getInt("id"));
		good.setGoodcode(rs.getInt("goodcode"));
		good.setGoodname(rs.getString("goodname"));
		good.setGoodprice(rs.getFloat("goodprice"));
		good.setGooddesc(rs.getString("gooddesc"));
		return good;
	}
	
	public Goods findByCode(String goodname) {
		//商品查找
		Connection conn = null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JdbcUtils.getConnection();
			String sql="select * from user where good_name=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, goodname);
			logger.debug(sql);
			rs=ps.executeQuery();
			
			while (rs.next()) {
				logger.debug("查询成功");
				return rowMap(rs);
			}
		}catch (Exception e) {
			logger.error("查询异常：{}",e.getMessage());
			throw new CommException("商品查询失败");
		}
		return null;
	}
	
	public void insert(Goods good) {
		//商品新增
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql="insert into Goods(good_code,good_name,good_price,good_desc) values(?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, good.getGoodcode());
			ps.setString(1, good.getGoodname());
			ps.setFloat(1, good.getGoodprice());
			ps.setString(1, good.getGooddesc());
			int ret = ps.executeUpdate();
			if(ret<1) {
				throw new CommException("商品新增失败");
			}
			logger.debug("商品新增成功");
		}catch (Exception e) {
			logger.debug("新增失败");
			throw new CommException("商品新增失败");
		}finally {
			JdbcUtils.close(conn, ps, rs);
		}
	}
	
	public void update(Goods good) {
		//商品修改
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql="update goods set good_code=?,good_name=?,good_price=?,good_desc=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, good.getGoodcode());
			ps.setString(1, good.getGoodname());
			ps.setFloat(1, good.getGoodprice());
			ps.setString(1, good.getGooddesc());
			int ret = ps.executeUpdate();
			if(ret<1) {
				throw new CommException("商品修改失败");
			}
			logger.debug("商品修改成功");
		}catch (Exception e) {
			logger.debug("修改失败");
			throw new CommException("商品修改失败");
		}finally {
			JdbcUtils.close(conn, ps, rs);
		}
	}
	
	public void delete(Integer id) {
		//商品删除
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql="delete from goods where id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int ret = ps.executeUpdate();
			if(ret<1) {
				throw new CommException("商品删除失败");
			}
			logger.debug("删除商品成功");
		}catch (Exception e) {
			logger.debug("删除失败");
			throw new CommException("删除商品失败");
		}finally {
			JdbcUtils.close(conn, ps, rs);
		}
	}
	
	/**
	 * 
	 * @param news
	 * @return
	 */
	public List<Goods> zhanPager(Goods goods) {
		List<Goods> goods1=new ArrayList<>();
		for(int i=0;i<10;i++) {
			Goods n=new Goods();
			
			n.setGoodname("仙女棒"+i);
			n.setGooddesc("新闻内容"+i);				
			n.setGoodprice((float) 9999.222);
			goods1.add(n);
		}
		logger.info("插入十条数据成功");
		return goods1;
	}
	
	
	public Goods findById(Integer id) {
		//1.获取数据库链接
		Connection conn=JdbcUtils.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			//2.准备sql语句
			String sql="select * from goods where ID=?";
			ps=conn.prepareStatement(sql);
			//3.给sql语句设置参数
			ps.setLong(1, id);
			
			logger.info("{}",sql);
			//4.执行sql语句
			rs=ps.executeQuery();
			//5.处理sql执行的结果
			while (rs.next()) {
				return rowMap(rs);
			}
			return null;
		} catch (Exception e) {
			throw new CommException("插入数据异常:"+e.getMessage());
		}finally {
			//6.关闭资源(链接、语句、结果集)
			JdbcUtils.close(conn, ps, rs);
		}
	}
	
	
	public List<Goods> findPager(Goods goods,Pager pager) {
		Connection conn=JdbcUtils.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql="select * from goods where good_name like ? and good_price like ?"
					+ " limit ?,?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+goods.getGoodname()+"%");
			ps.setString(2, "%"+goods.getGoodprice()+"%");
			ps.setInt(3, (pager.getCurrentPage()-1)*pager.getPageSize());
			ps.setInt(4, pager.getPageSize());
			
			logger.debug(sql);
			
			rs=ps.executeQuery();
			List<Goods> goodsl=new ArrayList<>();
			while (rs.next()) {
				goodsl.add(rowMap(rs));
			}

			return goodsl;
		} catch (Exception e) {
			logger.error(e.getMessage());
			throw new CommException(e.getMessage());
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
	}
	
	
	
}
